﻿---- chạy từng ngày, không nhảy cóc ngày thì sẽ thiếu giao dịch và thiếu số dư tín dụng.

declare @Tdate1 date set @Tdate1 = '2017-04-05'
declare @Tdate2 date set @Tdate2 = convert (datetime, @Tdate1) - 180
declare @Tdate3 date set @Tdate3 = convert (datetime, @Tdate1) - 181
declare @Tdate4 date set @Tdate4 = convert (datetime, @Tdate1) - 365


--select @Tdate1
--select @Tdate2
--select @Tdate3  
--select @Tdate4

EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##CUSTOMER'')
        DROP TABLE ##CUSTOMER')

exec('
SELECT RECID AS CIF,CUS_OPEN_DATE 
INTO ##CUSTOMER
FROM SERVER74.BICDATA.DBO.CUSTOMER 
WHERE SEGMENT = ''SMES'' AND CUS_OPEN_DATE <= '''+@Tdate1+'''
')


--1. DỮ LIỆU

--1.0 SỐ LIỆU GIAO DỊCH.
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##TRANSACTION_DAILY_2'')
        DROP TABLE ##TRANSACTION_DAILY_2')

EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##TRANSACTION_DAILY_1'')
        DROP TABLE ##TRANSACTION_DAILY_1')

EXEC('
SELECT * INTO ##TRANSACTION_DAILY_1
FROM SERVER16.[VPB_WHR2].[DBO].[TBL_VPB_STMT_ENTRY] A
WHERE CONVERT(DATE,BOOKING_DATE) = '''+@Tdate1+'''
')

EXEC('
SELECT A.*,B.INITIATION INTO ##TRANSACTION_DAILY_2
FROM ##TRANSACTION_DAILY_1 A
LEFT JOIN AA_WK.DBO.CODE_TRANSACTION B--DATA.DBO.TRANSACTION_CODE_ACTIVE B
ON A.[TRANSACTION_CODE]=B.ID COLLATE DATABASE_DEFAULT
WHERE CUSTOMER_ID IN (SELECT CIF FROM NEW_DATABASE.DBO.CUSTOMER_ID)
')

EXEC('
DELETE FROM SERVER12.TRANGLT9.DBO.TRANSACTION_DAILY WHERE BOOKING_DATE = '''+@Tdate1+'''
')

EXEC('
INSERT INTO SERVER12.TRANGLT9.DBO.TRANSACTION_DAILY
SELECT DISTINCT ACCOUNT_NUMBER,BOOKING_DATE,CUSTOMER_ID FROM ##TRANSACTION_DAILY_2
')

--1.1 DỮ LIỆU SỐ DƯ TÍN DỤNG (VAY)

EXEC('
DELETE FROM SERVER12.TRANGLT9.DBO.STATUS_CUSTOMER_LOAN WHERE BUSINESS_DATE = '''+@Tdate1+'''
')

EXEC('
INSERT INTO SERVER12.TRANGLT9.DBO.STATUS_CUSTOMER_LOAN
SELECT DISTINCT CIF,BUSINESS_DATE FROM SERVER74.BICDATA.DBO.BALANCE_LIVE
WHERE BUSINESS_DATE = '''+@Tdate1+'''
AND BI_SEGMENT = ''SME''
AND APP = ''LOAN''
')


---1.2 DỮ LIỆU SỐ DƯ TÍN DỤNG (LC)
EXEC('
DELETE FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_LC WHERE BUSINESS_DATE = '''+@Tdate1+'''
')

EXEC('
INSERT INTO SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_LC
SELECT DISTINCT CUST_ID AS CIF,BUSINESS_DATE FROM SERVER16.VPB_WHR2.[DBO].[LC_RPT_DAILY_VPB] 
WHERE BUSINESS_DATE = '''+@Tdate1+'''
')

---1.3 DỮ LIỆU SỐ DƯ TÍN DỤNG (MD)
EXEC('
DELETE FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_MD WHERE BUSINESS_DATE = '''+@Tdate1+'''
')

EXEC('
INSERT INTO SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_MD
SELECT DISTINCT CUST_ID AS CIF,VALUE_DATE AS BUSINESS_DATE 
FROM SERVER16.VPB_WHR2.[DBO].[MD_RPT_DAILY_VPB]
WHERE VALUE_DATE = '''+@Tdate1+'''
')



--- 1.4 DỮ LIỆU SỐ DƯ HUY ĐỘNG (CASA, TD, OTHER TD)TẠI NGÀY BÁO CÁO
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##BALANCE_LIVE'')
        DROP TABLE ##BALANCE_LIVE')

EXEC('
SELECT * INTO ##BALANCE_LIVE FROM SERVER74.BICDATA.DBO.BALANCE_LIVE WHERE BUSINESS_DATE = '''+@Tdate1+''' AND APP = ''DEPOSIT'' AND BI_SEGMENT <> ''RB''
')


EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##DEPOSIT'')
        DROP TABLE ##DEPOSIT')

EXEC('
SELECT DISTINCT CIF 
INTO ##DEPOSIT
FROM ##BALANCE_LIVE
WHERE BAL_QD > 0
AND CIF IN (SELECT CIF FROM NEW_DATABASE.DBO.CUSTOMER_ID)
')




--- 1.5 DỮ LIỆU PHÁT SINH HUY ĐỘNG (TD).
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##PRO_DEPOSIT'')
        DROP TABLE ##PRO_DEPOSIT')
		SELECT * 
		INTO DEPOSIT
		FROM SERVER74.BICDATA.DBO.DPTB_MASTER
		WHERE CIF IN (SELECT CIF FROM NEW_DATABASE.DBO.CUSTOMER_ID)

EXEC('
SELECT * 
INTO ##PRO_DEPOSIT
FROM (
	SELECT CIF FROM 
	WHERE (ISSUE_DATE >= '''+@Tdate2+''' AND ISSUE_DATE <='''+@Tdate1+''')
	AND PRODUCT_NAME = ''2. Term Deposit''
	UNION 
	SELECT CIF FROM NEW_DATABASE.DBO.DEPOSIT
	WHERE (ROLLOVER_DATE >= '''+@Tdate2+''' AND ROLLOVER_DATE <= '''+@Tdate1+''')
	AND PRODUCT_NAME = ''2. Term Deposit'' 
	) X
')


--2. XỬ LÝ DỮ LIỆU GIAO DỊCH.

-- 2.0 DANH SÁCH TÀI KHOẢN.
--DROP TABLE ##CURRENT_ACCOUNT
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##CURRENT_ACCOUNT'')
        DROP TABLE ##CURRENT_ACCOUNT')

EXEC('
SELECT ACCTNO,CIF 
INTO ##CURRENT_ACCOUNT
FROM NEW_DATABASE.DBO.DEPOSIT
WHERE CATEGORY IN (''1001'',''1005'',''1007'',''1008'')
')

-- 2.1 DANH SÁCH TÀI KHOẢN ĐÓNG.
--DROP TABLE ##CURRENT_ACCOUNT_CLOSE

EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##CURRENT_ACCOUNT_CLOSE'')
        DROP TABLE ##CURRENT_ACCOUNT_CLOSE')

EXEC('
SELECT ACCTNO,CIF  
INTO ##CURRENT_ACCOUNT_CLOSE
FROM NEW_DATABASE.DBO.DEPOSIT
WHERE CATEGORY IN (''1001'',''1005'',''1007'',''1008'')
AND BI_SEGMENT = ''SME''
AND WITHDRAW_DATE <= '''+@Tdate1+'''
')




---- 3. BÁO CÁO.

-- 3.1 CÓ GIAO DỊCH TÀI CHÍNH CHỦ ĐỘNG QUA TKTT VA TKTT CON HOAT DONG TAI THOI DIEM BAO CAO. (TẠO BẢNG GDTCCD)
-- DROP TABLE ##GDTCCD
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##GDTCCD'')
        DROP TABLE ##GDTCCD')


EXEC('
SELECT DISTINCT CUSTOMER_ID 
INTO ##GDTCCD
FROM SERVER12.TRANGLT9.DBO.TRANSACTION_DAILY  --<----- ĐẨY THÊM DỮ LIỆU VÀO ĐÂY.
WHERE (BOOKING_DATE >= '''+@Tdate2+''' AND BOOKING_DATE <= '''+@Tdate1+''') 
AND ACCOUNT_NUMBER IN (SELECT ACCTNO FROM ##CURRENT_ACCOUNT WHERE ACCTNO NOT IN (SELECT ACCTNO FROM ##CURRENT_ACCOUNT_CLOSE))
')

EXEC('
ALTER TABLE ##CUSTOMER ADD GDTCCD VARCHAR(8)
')


EXEC('
UPDATE ##CUSTOMER
SET GDTCCD = CASE WHEN CIF IN (SELECT CUSTOMER_ID FROM ##GDTCCD) THEN ''1'' ELSE ''0'' END
')

-- 3.2 KHÁCH HÀNG PHÁT SINH VAY SỐ DƯ TÍN DỤNG (VAY)
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_LD_1'')
        DROP TABLE ##LOAN_LD_1')


EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_LD_1
FROM SERVER12.TRANGLT9.DBO.STATUS_CUSTOMER_LOAN
WHERE BUSINESS_DATE >= '''+@Tdate2+''' AND BUSINESS_DATE <= '''+@Tdate1+'''
')


EXEC('
ALTER TABLE ##CUSTOMER ADD LOAN_LD VARCHAR(8)
')

EXEC('
UPDATE ##CUSTOMER
SET LOAN_LD = CASE WHEN CIF IN (SELECT CIF FROM ##LOAN_LD_1) THEN ''1'' ELSE ''0'' END
')


-- 3.3 KHÁCH HÀNG PHÁT SINH TÍN DỤNG (LC)

EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_LC_1'')
        DROP TABLE ##LOAN_LC_1')

EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_LC_1
FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_LC
WHERE BUSINESS_DATE >= '''+@Tdate2+''' AND BUSINESS_DATE <= '''+@Tdate1+'''
')


EXEC('
ALTER TABLE ##CUSTOMER ADD LOAN_LC VARCHAR(8)
')

EXEC('
UPDATE ##CUSTOMER
SET LOAN_LC = CASE WHEN CIF IN (SELECT CIF FROM ##LOAN_LC_1) THEN ''1'' ELSE ''0'' END 
')


-- 3.4 KHÁCH HÀNG PHÁT SINH TÍN DỤNG (MD)
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_MD_1'')
        DROP TABLE ##LOAN_MD_1')


EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_MD_1
FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_MD
WHERE BUSINESS_DATE >= '''+@Tdate2+''' AND BUSINESS_DATE <= '''+@Tdate1+'''
')


EXEC('
ALTER TABLE ##CUSTOMER ADD LOAN_MD VARCHAR(8)
')

EXEC('
UPDATE ##CUSTOMER
SET LOAN_MD = CASE WHEN CIF IN (SELECT CIF FROM ##LOAN_MD_1) THEN ''1'' ELSE ''0'' END 
')



-- 3.5 KHÁCH HÀNG CÓ SỐ DƯ HUY ĐỘNG (TD,CASA,OTHER TD) TẠI NGÀY BÁO CÁO.
EXEC('
ALTER TABLE ##CUSTOMER ADD DEPOSIT_BAL VARCHAR(8)
')

EXEC('
UPDATE ##CUSTOMER
SET DEPOSIT_BAL = CASE WHEN CIF IN (SELECT CIF FROM ##DEPOSIT) THEN ''1'' ELSE ''0'' END 
')


-- 3.6 KHÁCH HÀNG CÓ PHÁT SINH TD VÀ TÁI TỤC TRONG 6 THÁNG GẦN NHẤT.

EXEC('
ALTER TABLE ##CUSTOMER ADD DEPOSIT_PRO VARCHAR(8)
')

EXEC('
UPDATE ##CUSTOMER
SET DEPOSIT_PRO = CASE WHEN CIF IN (SELECT CIF FROM ##PRO_DEPOSIT) THEN ''1'' ELSE ''0'' END 
')


----- TRẠNG THÁI KHÁCH HÀNG.
EXEC('
ALTER TABLE ##CUSTOMER ADD [STATUS] VARCHAR(20)
')

EXEC('
UPDATE ##CUSTOMER
SET [STATUS] = ''ACTIVE''
FROM ##CUSTOMER A,( SELECT DISTINCT CIF FROM ##CUSTOMER
					WHERE GDTCCD = 1
					OR LOAN_LD = 1
					OR LOAN_LC =1 
					OR LOAN_MD =1
					OR DEPOSIT_BAL = 1
					OR DEPOSIT_PRO = 1
					) B
WHERE A.CIF = B.CIF
')


------------------------------ XÁC ĐỊNH DORMANT VÀ INACTIVE.

---- SẢN PHẨM TÀI KHOẢN THANH TOÁN.(6 - 12 THÁNG)
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##GDTCCD_DORMANT'')
        DROP TABLE ##GDTCCD_DORMANT')

EXEC('
SELECT DISTINCT CUSTOMER_ID 
INTO ##GDTCCD_DORMANT
FROM SERVER12.TRANGLT9.DBO.TRANSACTION_DAILY  --<----- ĐẨY THÊM DỮ LIỆU VÀO ĐÂY.
WHERE (BOOKING_DATE >= '''+@Tdate4+''' AND BOOKING_DATE <= '''+@Tdate3+''') 
AND ACCOUNT_NUMBER IN (SELECT ACCTNO FROM ##CURRENT_ACCOUNT WHERE ACCTNO NOT IN (SELECT ACCTNO FROM ##CURRENT_ACCOUNT_CLOSE))
')
---- SẢN PHÂM TÀI KHOẢN TIỀN GỬI CÓ KÌ HẠN (6 - 12 THÁNG).
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##PRO_DEPOSIT_DORMANT'')
        DROP TABLE ##PRO_DEPOSIT_DORMANT')


EXEC('
SELECT * 
INTO ##PRO_DEPOSIT_DORMANT
FROM (
	SELECT CIF FROM NEW_DATABASE.DBO.DEPOSIT
	WHERE (ISSUE_DATE >= '''+@Tdate4+''' AND ISSUE_DATE <= '''+@Tdate3+''')
	AND PRODUCT_NAME = ''2. Term Deposit'' 
	UNION 
	SELECT CIF FROM NEW_DATABASE.DBO.DEPOSIT
	WHERE (ROLLOVER_DATE >= '''+@Tdate4+''' AND ROLLOVER_DATE <= '''+@Tdate3+''')
	AND PRODUCT_NAME = ''2. Term Deposit'' 
	) X
')

---- SẢN PHẨM TÍN DỤNG (6 - 12 THÁNG). LOAN
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_LD_1_DORMANT'')
        DROP TABLE ##LOAN_LD_1_DORMANT')

EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_LD_1_DORMANT
FROM SERVER12.TRANGLT9.DBO.STATUS_CUSTOMER_LOAN
WHERE BUSINESS_DATE >= '''+@Tdate4+''' AND BUSINESS_DATE <= '''+@Tdate3+'''
')


---- SẢN PHẨM TÍN DỤNG (6 - 12 THÁNG). LC
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_LC_1_DORMANT'')
        DROP TABLE ##LOAN_LC_1_DORMANT')

EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_LC_1_DORMANT
FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_LC
WHERE BUSINESS_DATE >= '''+@Tdate4+''' AND BUSINESS_DATE <= '''+@Tdate3+'''
')

---- SẢN PHẨM TÍN DỤNG (6 - 12 THÁNG). MD
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_MD_1_DORMANT'')
        DROP TABLE ##LOAN_MD_1_DORMANT')

EXEC('
SELECT DISTINCT CIF
INTO ##LOAN_MD_1_DORMANT
FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_MD
WHERE BUSINESS_DATE >= '''+@Tdate4+''' AND BUSINESS_DATE <= '''+@Tdate3+'''
')


---- CÓ TÍN DỤNG DORMANT (6- 12)
EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##LOAN_DORMANT'')
        DROP TABLE ##LOAN_DORMANT')

EXEC('
SELECT * 
INTO ##LOAN_DORMANT
FROM (
	SELECT * FROM ##LOAN_LD_1_DORMANT
	UNION
	SELECT * FROM ##LOAN_LC_1_DORMANT
	UNION
	SELECT * FROM ##LOAN_MD_1_DORMANT
	) X
')

EXEC('IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''##DORMANT'')
        DROP TABLE ##DORMANT')

EXEC('
SELECT * 
INTO ##DORMANT
FROM (
	SELECT CUSTOMER_ID AS CIF FROM ##GDTCCD_DORMANT
	UNION 
	SELECT * FROM ##PRO_DEPOSIT_DORMANT
	UNION
	SELECT * FROM ##LOAN_DORMANT
	) X
')

EXEC('
UPDATE ##CUSTOMER
SET [STATUS] = ''DORMANT''
FROM ##CUSTOMER A,(SELECT * FROM ##DORMANT) B
WHERE A.CIF = B.CIF
AND A.[STATUS] IS NULL
')


EXEC('
UPDATE ##CUSTOMER
SET [STATUS] = ''INACTIVE''
WHERE [STATUS] IS NULL
')

EXEC('
--DELETE FROM ACTIVE_CUSTOMER_DAILY
')

--DROP TABLE AD_HOC.DBO.ACTIVE_CUSTOMER_DAILY

EXEC('
--INSERT INTO ACTIVE_CUSTOMER_DAILY
SELECT *,REPORT_DATE = '''+@Tdate1+'''
INTO ACTIVE_CUSTOMER_DAILY 
FROM ##CUSTOMER
')


EXEC('
DELETE FROM ACTIVE_CUSTOMER_DAILY_REPORT
')

EXEC('
--INSERT INTO ACTIVE_CUSTOMER_DAILY_REPORT
SELECT A.REPORT_DATE,A.CIF,B.CUSTOMER_NAME,B.BRANCH_CODE,C.BRANCH_NAME_SME,C.VUNG,B.DAO,D.PHAN_KHUC AS SUB_SEGMENT,E.[LAND LINE],E.MOBILE,E.OFFICE,
F.[GIAM DOC],F.[KE TOAN TRUONG],F.[PHO GIAM DOC]
,A.[STATUS] 
INTO ACTIVE_CUSTOMER_DAILY_REPORT
FROM ACTIVE_CUSTOMER_DAILY A
LEFT JOIN NEW_DATABASE.DBO.CUSTOMER_ID B ON A.CIF = B.CIF
LEFT JOIN DWH.DBO.BRANCH_CODE C ON B.BRANCH_CODE = C.BRANCH_CODE COLLATE DATABASE_DEFAULT
LEFT JOIN (SELECT * FROM SERVER12.TRANGLT9.DBO.CUSTOMER WHERE DATA_DATE IN (SELECT MAX(DATA_DATE) FROM SERVER12.TRANGLT9.DBO.CUSTOMER)) D ON A.CIF = D.RECID
LEFT JOIN NEW_DATABASE.DBO.TBL_CUSTOMER_CONTACT E ON A.CIF = E.RECID
LEFT JOIN NEW_DATABASE.DBO.TBL_CUSTOMER_RELATION F ON A.CIF = F.RECID
')

--EXEC('
--DELETE FROM SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_DAILY_REPORT
--')

EXEC('
INSERT INTO SERVER12.TRANGLT9.DBO.ACTIVE_CUSTOMER_DAILY_REPORT
SELECT * FROM ACTIVE_CUSTOMER_DAILY_REPORT
')

